from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from fastapi import HTTPException
from models.user_stats import UserStats
from models.user import User
from schemas.user_stats import UserStatsCreate, UserStatsUpdate, TTSStatsRequest, ASRStatsRequest
from datetime import date, datetime, timedelta
from typing import List, Optional

# 验证用户是否存在
def verify_user_exists(db: Session, user_id: int) -> bool:
    """验证用户是否存在"""
    user = db.query(User).filter(User.id == user_id).first()
    return user is not None

# 获取或创建用户统计记录
def get_or_create_user_stats(db: Session, user_id: int, stat_date: date) -> UserStats:
    """获取或创建用户统计记录"""
    try:
        # 验证用户是否存在
        if not verify_user_exists(db, user_id):
            raise HTTPException(status_code=404, detail=f"用户ID {user_id} 不存在")
        
        # 查找当天的统计记录
        stats = db.query(UserStats).filter(
            UserStats.user_id == user_id,
            UserStats.stat_date == stat_date
        ).first()
        
        if not stats:
            # 创建新的统计记录
            stats = UserStats(
                user_id=user_id,
                stat_date=stat_date,
                tts_count=0,
                asr_seconds=0
            )
            db.add(stats)
            db.commit()
            db.refresh(stats)
        
        return stats
        
    except IntegrityError as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=f"数据完整性错误: {str(e)}")
    except SQLAlchemyError as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

# 更新TTS统计
def update_tts_stats(db: Session, request: TTSStatsRequest):
    """更新用户TTS统计"""
    try:
        # 处理日期：如果没有指定日期，使用今天；如果指定了字符串日期，转换为date对象
        if request.date is None:
            stat_date = date.today()
        else:
            try:
                stat_date = date.fromisoformat(request.date)
            except ValueError:
                raise HTTPException(status_code=400, detail="日期格式错误，请使用YYYY-MM-DD格式")
        
        # 获取或创建统计记录
        stats = get_or_create_user_stats(db, request.user_id, stat_date)
        
        # 更新TTS文字数量
        stats.tts_count += request.text_length
        
        db.commit()
        db.refresh(stats)
        
        return {"message": "TTS统计更新成功", "tts_count": stats.tts_count}
        
    except HTTPException:
        raise
    except SQLAlchemyError as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

# 更新ASR统计
def update_asr_stats(db: Session, request: ASRStatsRequest):
    """更新用户ASR统计"""
    try:
        # 处理日期：如果没有指定日期，使用今天；如果指定了字符串日期，转换为date对象
        if request.date is None:
            stat_date = date.today()
        else:
            try:
                stat_date = date.fromisoformat(request.date)
            except ValueError:
                raise HTTPException(status_code=400, detail="日期格式错误，请使用YYYY-MM-DD格式")
        
        # 获取或创建统计记录
        stats = get_or_create_user_stats(db, request.user_id, stat_date)
        
        # 更新ASR音频时长
        stats.asr_seconds += request.seconds
        
        db.commit()
        db.refresh(stats)
        
        return {"message": "ASR统计更新成功", "asr_seconds": stats.asr_seconds}
        
    except HTTPException:
        raise
    except SQLAlchemyError as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

# 获取用户统计
def get_user_stats(db: Session, user_id: int, start_date: Optional[date] = None, end_date: Optional[date] = None) -> List[dict]:
    """获取用户统计信息"""
    try:
        # 验证用户是否存在
        if not verify_user_exists(db, user_id):
            raise HTTPException(status_code=404, detail=f"用户ID {user_id} 不存在")
        
        query = db.query(UserStats).filter(UserStats.user_id == user_id)
        
        # 如果指定了日期范围
        if start_date:
            query = query.filter(UserStats.stat_date >= start_date)
        if end_date:
            query = query.filter(UserStats.stat_date <= end_date)
        
        # 按日期排序
        stats_list = query.order_by(UserStats.stat_date).all()
        
        # 转换为响应格式
        result = []
        for stats in stats_list:
            result.append({
                "date": stats.stat_date.strftime("%Y-%m-%d"),
                "tts_count": stats.tts_count,
                "asr_seconds": stats.asr_seconds
            })
        
        return result
        
    except HTTPException:
        raise
    except SQLAlchemyError as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

# 获取用户今日统计
def get_user_today_stats(db: Session, user_id: int) -> Optional[dict]:
    """获取用户今日统计"""
    try:
        # 验证用户是否存在
        if not verify_user_exists(db, user_id):
            raise HTTPException(status_code=404, detail=f"用户ID {user_id} 不存在")
        
        today = date.today()
        stats = db.query(UserStats).filter(
            UserStats.user_id == user_id,
            UserStats.stat_date == today
        ).first()
        
        if stats:
            return {
                "date": stats.stat_date.strftime("%Y-%m-%d"),
                "tts_count": stats.tts_count,
                "asr_seconds": stats.asr_seconds
            }
        else:
            return {
                "date": today.strftime("%Y-%m-%d"),
                "tts_count": 0,
                "asr_seconds": 0
            }
            
    except HTTPException:
        raise
    except SQLAlchemyError as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

# 获取用户本月统计
def get_user_month_stats(db: Session, user_id: int, year: int, month: int) -> List[dict]:
    """获取用户指定月份的统计"""
    try:
        # 验证用户是否存在
        if not verify_user_exists(db, user_id):
            raise HTTPException(status_code=404, detail=f"用户ID {user_id} 不存在")
        
        # 计算月份的开始和结束日期
        start_date = date(year, month, 1)
        if month == 12:
            end_date = date(year + 1, 1, 1) - timedelta(days=1)
        else:
            end_date = date(year, month + 1, 1) - timedelta(days=1)
        
        return get_user_stats(db, user_id, start_date, end_date)
        
    except HTTPException:
        raise
    except SQLAlchemyError as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

# 获取所有用户的今日统计汇总
def get_all_users_today_summary(db: Session) -> List[dict]:
    """获取所有用户的今日统计汇总"""
    try:
        today = date.today()
        stats = db.query(UserStats).filter(UserStats.stat_date == today).all()
        
        result = []
        for stat in stats:
            result.append({
                "user_id": stat.user_id,
                "date": stat.stat_date.strftime("%Y-%m-%d"),
                "tts_count": stat.tts_count,
                "asr_seconds": stat.asr_seconds
            })
        
        return result
        
    except SQLAlchemyError as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

# 全局统计函数
def get_global_today_stats(db: Session) -> dict:
    """获取所有用户今日统计汇总"""
    try:
        today = date.today()
        stats = db.query(UserStats).filter(UserStats.stat_date == today).all()
        
        total_tts = sum(stat.tts_count for stat in stats)
        total_asr = sum(stat.asr_seconds for stat in stats)
        
        return {
            "date": today.strftime("%Y-%m-%d"),
            "tts_count": total_tts,
            "asr_seconds": total_asr,
            "user_count": len(stats)
        }
        
    except SQLAlchemyError as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

def get_global_month_stats(db: Session, year: int, month: int) -> dict:
    """获取所有用户指定月份统计汇总"""
    try:
        # 计算月份的开始和结束日期
        start_date = date(year, month, 1)
        if month == 12:
            end_date = date(year + 1, 1, 1) - timedelta(days=1)
        else:
            end_date = date(year, month + 1, 1) - timedelta(days=1)
        
        stats = db.query(UserStats).filter(
            UserStats.stat_date >= start_date,
            UserStats.stat_date <= end_date
        ).all()
        
        total_tts = sum(stat.tts_count for stat in stats)
        total_asr = sum(stat.asr_seconds for stat in stats)
        
        return {
            "year": year,
            "month": month,
            "tts_count": total_tts,
            "asr_seconds": total_asr,
            "user_count": len(set(stat.user_id for stat in stats))
        }
        
    except SQLAlchemyError as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")

def get_global_all_stats(db: Session) -> dict:
    """获取所有用户历史统计汇总"""
    try:
        stats = db.query(UserStats).all()
        
        total_tts = sum(stat.tts_count for stat in stats)
        total_asr = sum(stat.asr_seconds for stat in stats)
        
        return {
            "tts_count": total_tts,
            "asr_seconds": total_asr,
            "user_count": len(set(stat.user_id for stat in stats)),
            "total_days": len(set(stat.stat_date for stat in stats))
        }
        
    except SQLAlchemyError as e:
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}") 